#coding=utf-8
#!/usr/bin/python


# cd /d D:\python2.6.6\dev_test 
#     cd /d D:\python26 
#     python.exe D:\py\demo\updb.py

""" 

数据库操作
MySQL-python-1.2.3c1.win32-py2.6.exe
"""
import os, sys, string
try:
    import MySQLdb
except Exception, e:
    print e
    print __file__
    sys.exit()
    



# 连接数据库　
"""
1	conn = MySQLdb.connect (host = "localhost",
02	                           user = "root",
03	                           passwd = "xxxx",
04	                           db = "xxxx")
05	    cursor = conn.cursor ()
06	    cursor.execute ("SELECT VERSION()")
07	    row = cursor.fetchone ()
08	    print "server version:", row[0]
09	    cursor.close ()
10	    conn.close ()
cursor.description=(name,type_code,display_size,internal_size,precision,scale,null_ok)
"""
class db_cls():
    def __init__(self): 
        "构建"
        pass
    def __del__(self):
        "析构"
        if (self.init['state']  != 0): #未初始化
            pass
        #if self.con:
        #self.con.close()
    def init(self,arg_host='localhost',arg_user='root',arg_passwd=''):
        "初始化"
        ret_val = {'error':'not init','state':0}#成功0 失败!=0
        try:
            self.con = MySQLdb.connect(
                            host = arg_host,
                            user = arg_user,
                            passwd = arg_passwd,charset='utf8')

            
            ret_val['error']=''

        except Exception, e:
            ret_val['error']=e
            ret_val['state']=1
            print ret_val['error']
             
        self.init_state = ret_val #初始化 结果
        return ret_val
    def sel_db(self,sql):
        "选择数据库"
        if (self.init_state['state']  != 0): #未初始化
            return self.init_state
           
           
    def test(self):
        "测试"
        if ( self.init_state['state']  != 0): #未初始化
            return self.init_state
        cursor = self.con.cursor()
        
        self.do_sql('use wsnew ');


        cursor.execute ("SELECT t.* FROM tmp_table t limit 5")
        
        # [dict(zip([j[0] for j in cursor.description], i)) for i in records]      
        
        #强大 高级
        records = cursor.fetchall()
        field = [i[0] for i in cursor.description]
        records2 = [dict(zip(field,i)) for i in records]

        print records2[0]['联系人'] 
        
        print type(cursor.description)

        #print cursor.description

        row = cursor.fetchone ()
        print 'count=',cursor.rowcount     #结果行数
        cursor.close ()
        #self.con.close ()
        #sys.exit()
    def do_sql(self,sql,sql_arg=''):
        "执行"
        if (self.init_state['state']  != 0): #未初始化
            return self.init_state
        cursor = self.con.cursor()
        state = cursor.execute(sql)
        self.con.commit() #居然 插入要提交
        cursor.close ()
        return state

    def get_insert_id(self):
        "插入的id"
        if (self.init_state['state']  != 0):
            return self.init_state
        sql = ' SELECT LAST_INSERT_ID() AS id '
        row = self.get_one(sql)
        return row['id']
        
        
    def get_one(self,sql):
        "一条"
        if (self.init_state['state']  != 0): #未初始化
            return self.init_state
        cursor = self.con.cursor()
        #print sql
        state = cursor.execute (sql)
        #if state:
        #    print 'sql error'
        #row = cursor.fetchone()
        
        #高级
        records = cursor.fetchone()
        if records is None:
            print 'sql error'
            return {}
            
        field = [i[0] for i in cursor.description]
        records2 = dict(zip(field,records))
        return records2
    def get_all(self,sql):
        "所有"
        if (self.init_state['state']  != 0): #未初始化
            return self.init_state
        cursor = self.con.cursor()
        cursor.execute (sql)
        #row = cursor.fetchall()
        
        #高级
        records = cursor.fetchall()
        self.rowcount = cursor.rowcount
        field = [i[0] for i in cursor.description]
        records2 = [dict(zip(field,i)) for i in records]

        return records2

    
if __name__ == '__main__':
    db = db_cls()
    #db.init('china.worldscrap.com','root','woshiug')
    db.init('192.168.0.249','root','0air.com')
    db.do_sql('use sms_v2test ');
    db.do_sql(' set names utf8 ');


    print db.do_sql('insert  into ws_to_v2 (wsid,v2id)values( 111,222) ')

    sys.exit()

